package dao;

import entity.Student;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
public class StudentDao implements IStudentDao {
    @Override
    public int add(Student s) {
        return 0;
    }

    @Override
    public int update(Student s) {
        return 0;
    }

    @Override
    public int del(long stuno) {
        return 0;
    }

    @Override
    public Student get(long stuno) {
        return null;
    }

    @Override
    public List<Student> find(int pageNo, int pageSize) {
        return null;
    }

    @Override
    public List<Student> search(int pageNo, int pageSize, String sname, String qq, String phone, Date enterStart, Date enterEnd) {
        List<Student> datas = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object> params=new ArrayList<>();//可用参数

        String sql = "select * from s_student where 1=1";
        if (sname != null && !sname.equals(""))
        {
            sql += " and sname like concat('%',?,'%')";
            params.add(sname);
        }
        if (qq != null && !qq.equals(""))
        {
            sql += " and qq like concat('%',?,'%')";
            params.add(qq);
        }
        if (phone != null && !phone.equals(""))
        {
            sql += " and telephone like concat('%',?,'%')";
            params.add(phone);
        }
        if (enterStart != null )
        {
            sql += " and enterdate >=?";
            params.add(enterStart);
        }
        if (enterEnd != null )
        {
            sql += " and enterdate <=?";
            params.add(enterEnd);
        }
        sql += " limit ?,?";
        params.add((pageNo - 1) * pageSize);
        params.add(pageSize);
        System.out.println(sql+">>>>"+params);
        try {
            ps = connection.prepareStatement(sql);
            for(int i=0;i<params.size();i++){
                ps.setObject(i+1,params.get(i));
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getInt("stuno"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setTelephone(rs.getString("telephone"));
                student.setQq(rs.getString("qq"));
                student.setWechat(rs.getString("wechat"));
                student.setGradeid(rs.getInt("gradeid"));
                student.setEnterdate(rs.getDate("enterdate"));
                student.setBirthday(rs.getDate("birthday"));
                student.setFromcity(rs.getString("fromcity"));
                student.setAddress(rs.getString("address"));
                student.setPass(rs.getString("pass"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return datas;
    }

    @Override
    public int searchTotal(String sname, String qq, String phone, Date enterStart, Date enterEnd) {
        return 0;
    }
}
